package com.wl156.model;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class Database {

	private static final String TAG = Database.class.getSimpleName();
	private static String DB_PATH = "/sdcard/Android/data/com.wl156/";
	private static String DB_NAME = "chinaall.db";

	private SQLiteDatabase myDataBase;
	private final Context myContext;

	public Database(Context context) {
		myContext = context;
		try {
			createDataBase();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private static Database sDatabase;

	public static Database getInstance(Context context) {
		if (sDatabase == null) {
			sDatabase = new Database(context);
		}
		return sDatabase;
	}

	public void createDataBase() throws IOException {
		boolean dbExist = checkDataBase();
		if (dbExist) {
		} else {
			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	private boolean checkDataBase() {
		SQLiteDatabase checkDB = null;
		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);
		} catch (Exception e) {
		}

		if (checkDB != null) {
			checkDB.close();
		}

		return checkDB != null ? true : false;
	}

	public void copyDataBase() throws IOException {

		InputStream myInput = null;
		myInput = myContext.getAssets().open(DB_NAME);

		String outFileName = DB_PATH + DB_NAME;

		File file = new File(outFileName);
		File dir = new File(file.getParent());
		if (!dir.isDirectory()) {
			if (!dir.mkdirs()) {
				android.util.Log.i(TAG,
						"mkdir failed. dir: " + file.getParent());
				return;
			}
		}
		if (!file.exists()) {
			try {
				file.createNewFile();
			} catch (IOException e) {
				android.util.Log.i(TAG, "create file failed.");
				return;
			}
		}

		OutputStream myOutput = new FileOutputStream(file);

		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public void openDataBase() throws SQLException {
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY);
	}

	SQLiteDatabase mSQLiteDatabase;

	public SQLiteDatabase getWritableDatabase() {
		String myPath = DB_PATH + DB_NAME;
		mSQLiteDatabase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);
		return mSQLiteDatabase;
	}

	public SQLiteDatabase getReadableDatabase() {
		if (mSQLiteDatabase != null) {
			return mSQLiteDatabase;
		} else {
			return getWritableDatabase();
		}
	}

	public synchronized void close() {
		if (myDataBase != null)
			myDataBase.close();
	}

	public Cursor selectProvice() {
		String sql = "select ProSort as _id, ProName from T_Province";
		return getReadableDatabase().rawQuery(sql, null);
	}

	public Cursor selectCity(long id) {
		String sql = "select CitySort as _id, CityName from T_City where ProID = ?";
		return getReadableDatabase().rawQuery(sql,
				new String[] { String.valueOf(id) });
	}

	public Cursor selectZone(long id) {
		String sql = "select ZoneID as _id, ZoneName from T_Zone where CityID = ?";
		return getReadableDatabase().rawQuery(sql,
				new String[] { String.valueOf(id) });
	}

	public String getProviceCapital(long id) {
		String sql = "select CitySort as _id, CityName, IsCapital from T_City where ProID = ? and IsCapital = 1";
		Cursor c = getReadableDatabase().rawQuery(sql,
				new String[] { String.valueOf(id) });
		if (c.moveToFirst()) {
			return c.getString(1);
		}
		return "";
	}

	public String getProvinceCapital(String provinceName) {
		return getProviceCapital(getProviceIdByName(provinceName));
	}

	public long getProviceIdByName(String provice) {
		String sql = "select ProSort from T_Province where ProName = ?";
		Cursor c = getReadableDatabase()
				.rawQuery(sql, new String[] { provice });
		long id = -1;
		if (c.moveToFirst()) {
			id = c.getLong(0);
		}
		return id;
	}

	public long getCityIdByName(String city) {
		String sql = "select CitySort from T_City where CityName = ?";
		Cursor c = getReadableDatabase().rawQuery(sql, new String[] { city });
		long id = -1;
		if (c.moveToFirst()) {
			id = c.getLong(0);
		}
		return id;
	}
}
